//
//  VGDBManager.swift
//  DBTest
//
//  Created by 宋海胜 on 2019/11/26.
//  Copyright © 2019 宋海胜. All rights reserved.
//

import UIKit
import SQLite


let status_column = Expression<String>("status")
let priority_column = Expression<String>("priority")
let className_column = Expression<String>("classname")
let data_column = Expression<String>("data")
let runCount_column = Expression<Int>("runcount")
let customID_column = Expression<Int>("customid")
let userID_column = Expression<String>("userid")
let orgID_column = Expression<String>("orgid")
let picUrl_column = Expression<String>("picurl")
let thumbUrl_column = Expression<String>("thumburl")
let describe_column = Expression<String>("describe")
let nickname_column = Expression<String>("nickname")
let oname_column = Expression<String>("oname")
let napicUrl_column = Expression<String>("napicurl")
let publishTime_column = Expression<String>("publishtime")

let title_column = Expression<String>("title")
let content_column = Expression<String>("content")

let authID_column = Expression<String>("authid")
let logo_column = Expression<String>("logo")
let otype_column = Expression<String>("otype")


/// 后台任务数据库
class VGDBManager {
    
    var db: Connection!
    // MARK: - properties
    static let sharedInstance:VGDBManager = VGDBManager();
    
    // MARK: - life cycle
    private init() {
        
        self.createDB()
    }
    
    // MARK: -  public
    public func insertTasks(_ tasks: Table, _ item: VGDBModel) -> Int64 {
        
        let insert = tasks.insert(rowid <- item.taskID,
                                  status_column <- item.status,
                                  priority_column <- item.priority,
                                  className_column <- item.className,
                                  data_column <- item.data,
                                  runCount_column <- item.runCount,
                                  customID_column <- item.customID,
                                  userID_column <- item.userID,
                                  orgID_column <- item.orgID,
                                  title_column <- item.title,
                                  picUrl_column <- item.picUrl,
                                  thumbUrl_column <- item.thumbUrl,
                                  describe_column <- item.describe,
                                  
                                  nickname_column <- item.nickname,
                                  oname_column <- item.oname,
                                  napicUrl_column <- item.napicUrl,
                                  publishTime_column <- item.publishTime,
                                  
                                  authID_column <- item.authID,
                                  logo_column <- item.logo,
                                  otype_column <- item.otype,
                                  content_column <- item.content)
        if let rowId = try? db.run(insert) {
            print("插入成功：\(rowId)")
            return rowId
        } else {
            print("插入失败")
            return 0
        }
    }
    
    public func deleteTask(_ tasks: Table, _ id: Int64) {
        
        let alice = tasks.filter(id == rowid)
        if let count = try? db.run(alice.delete()) {
            print("删除的条数为：\(count)")
        } else {
            print("删除失败")
        }
    }
    
    public func deleteTasks(_ tasks: Table) {
        
        let alice = tasks.filter(status_column == "init")
        if let count = try? db.run(alice.delete()) {
            print("删除的条数为：\(count)")
        } else {
            print("删除失败")
        }
    }
    
    public func updateTasks(_ tasks: Table, _ id: Int64, _ item: VGDBModel) {
        
        let task = tasks.filter(id == rowid)
        if let count = try? db.run(task.update(rowid <- item.taskID,
                                                status_column <- item.status,
                                                priority_column <- item.priority,
                                                className_column <- item.className,
                                                data_column <- item.data,
                                                runCount_column <- item.runCount,
                                                customID_column <- item.customID,
                                                userID_column <- item.userID,
                                                orgID_column <- item.orgID,
                                                title_column <- item.title,
                                                picUrl_column <- item.picUrl,
                                                thumbUrl_column <- item.thumbUrl,
                                                describe_column <- item.describe,
                                                nickname_column <- item.nickname,
                                                oname_column <- item.oname,
                                                napicUrl_column <- item.napicUrl,
                                                publishTime_column <- item.publishTime,
                                                
                                                authID_column <- item.authID,
                                                logo_column <- item.logo,
                                                otype_column <- item.otype,
                                                content_column <- item.content)) {
            print("修改的条数为：\(count)")
        } else {
            print("修改失败")
        }
    }
    
    // 查所有
    public func prepareTasks(_ tasks: Table) -> [VGDBModel] {
        
        var dbModels: [VGDBModel] = []
        let query = tasks.filter(status_column == "init")//select(data_column).order(rowid)
        do {
            for task in try db.prepare(query) {
                
                let dbModel: VGDBModel = VGDBModel()
                dbModel.taskID = task[rowid]
                dbModel.status = task[status_column]
                dbModel.priority = task[priority_column]
                dbModel.className = task[className_column]
                dbModel.data = task[data_column]
                dbModel.runCount = task[runCount_column]
                dbModel.customID = task[customID_column]
                dbModel.userID = task[userID_column]
                dbModel.picUrl = task[picUrl_column]
                dbModel.thumbUrl = task[thumbUrl_column]
                dbModel.describe = task[describe_column]
                dbModel.orgID = task[orgID_column]
                dbModel.title = task[title_column]
                dbModel.content = task[content_column]
                
                dbModel.nickname = task[nickname_column]
                dbModel.oname = task[oname_column]
                dbModel.napicUrl = task[napicUrl_column]
                dbModel.publishTime = task[publishTime_column]
                
                dbModel.authID = task[authID_column]
                dbModel.logo = task[logo_column]
                dbModel.otype = task[otype_column]
                dbModels.append(dbModel)
            }
        } catch {
            print(error)
        }
        return dbModels
    }
    
    // 查单个
    public func prepareTasksByRowId(_ tasks: Table, _ id: Int64) -> [VGDBModel] {
        
        var dbModels: [VGDBModel] = []
        let query = tasks.filter(rowid == id)//select(data_column).order(rowid)
        do {
            for task in try db.prepare(query) {
                
                let dbModel: VGDBModel = VGDBModel()
                dbModel.taskID = task[rowid]
                dbModel.status = task[status_column]
                dbModel.priority = task[priority_column]
                dbModel.className = task[className_column]
                dbModel.data = task[data_column]
                dbModel.runCount = task[runCount_column]
                dbModel.customID = task[customID_column]
                dbModel.userID = task[userID_column]
                dbModel.picUrl = task[picUrl_column]
                dbModel.thumbUrl = task[thumbUrl_column]
                dbModel.describe = task[describe_column]
                dbModel.orgID = task[orgID_column]
                dbModel.title = task[title_column]
                dbModel.content = task[content_column]
                
                dbModel.nickname = task[nickname_column]
                dbModel.oname = task[oname_column]
                dbModel.napicUrl = task[napicUrl_column]
                dbModel.publishTime = task[publishTime_column]
                
                dbModel.authID = task[authID_column]
                dbModel.logo = task[logo_column]
                dbModel.otype = task[otype_column]
                dbModels.append(dbModel)
            }
        } catch {
            print(error)
        }
        return dbModels
    }

    
    // MARK: - private methods
    fileprivate func createDB() {
        
        if db == nil {
            
            let path = NSSearchPathForDirectoriesInDomains(
                .documentDirectory, .userDomainMask, true
                ).first!
            print("连接后台任务数据库---\(path)/db.sqlite3")
            db = try! Connection("\(path)/db.sqlite3")
            db.busyTimeout = 5.0
            
            db.busyHandler({ tries in
                if tries >= 5 {
                    return false
                }
                return true
            })
        }
        self.createTaskTable()
    }
    
    fileprivate func createTaskTable() {
            
        let tasks = self.tasksTableName(.uploadTask)!
        do {
            try db.run(tasks.create(temporary: false, ifNotExists: true, withoutRowid: false, block: { (t) in
                
                t.column(rowid)
                t.column(status_column)
                t.column(priority_column)
                t.column(className_column)
                t.column(data_column)
                t.column(runCount_column)
                t.column(customID_column)
                t.column(userID_column)
                t.column(picUrl_column)
                t.column(thumbUrl_column)
                t.column(describe_column)
                t.column(orgID_column)
                t.column(title_column)
                t.column(content_column)
                
                t.column(nickname_column)
                t.column(oname_column)
                t.column(napicUrl_column)
                t.column(publishTime_column)
                
                t.column(authID_column)
                t.column(logo_column)
                t.column(otype_column)
            })
            )
        } catch {
            print(error)
        }
    }
    
    public func tasksTableName(_ dataType:VGDictionaryDataType) -> Table? {
        
        if (dataType == .uploadTask) {
            return Table("tab_upload_task");
        }
        return nil
    }
}


class VGDBModel: NSObject {
    
    var taskID: Int64 = 0 // 自增Id，当task被添加到队列，写入数据库中时会自动加1
    var status: String = "" // init、running、suspend、finish、fail、remove六种状态，在taskmodel刚被写入数据中时是init的状态，在taskModel执行失败后会将状态置为fail
    var priority: String = "" // Task的优先级，默认值为low，在taskQueue中可以根据task的优先级对高优先级的任务优先调度执行
    var className: String = "" // 存储具体任务的类型如TaskModelA
    var data: String = "" // 储TaskModel归档后的data数据
    var runCount: Int = 0 // 存储task执行的次数，可以用于控制重试次数
    var customID: Int = 0 // 对task做某种标记，方便从数据库查询
    
    var userID: String = ""
    
    var authID: String = ""
    var logo: String = ""
    var otype: String = ""
    
    var orgID: String = "" // 对task做某种标记，方便从数据库查询
    var title: String = "" // 动态标题
    var content: String = "" // 动态文本
    var picUrl: String = "" // 高清图
    var thumbUrl: String = "" // 缩略图
    var videoUrl: String = "" // 视频
    var describe: String = "" // 描述
    var nickname: String = "" // 昵称
    var oname: String = "" // 机构名
    var napicUrl: String = "" // 头像
    var publishTime: String = "" // 发布时间
}

enum VGDictionaryDataType {
    case uploadTask
}


